
#region USING
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Timers;
#endregion
namespace GeneralClasses
{

    #region NOTES
    /* 11 NOV 2010 - changing container objects to table IDs and fixing load/store operations to do just the relation tables
     * 10 NOV 2010 - adding in try catches to all database calls and constructing public pointers back to container class to implement the public additions to the master lists
     * 8 NOV 2010 - manager status is now boolean, manager and phone numbers will be in the sql table, workerID taen out
     * 3 NOV 2010 - cleaning it up
     * 24 OCt 2010 adding in features to call and add data, without the tables up and running, this is still only guesswork
     * 13 OCT 2010 - Created class, cant put it in so nothing is tested, still writing in notepadd++.
     */
    #endregion

    /// <summary>
    /// container class that holds information on the person and personal info, like vacations, preferrred departments, etc
    /// </summary>
    public class Person
    {

        #region VARIABLES
        //==============================================================================
        //                                                                     VARIABLES
        //==============================================================================

        private int perid;

        private String firstName; // names are separate to do things like "Doe, J" or "J Doe" or "J D" etc
        private String middleName;
        private String lastName;
        private String username; // username and password are separate, and in a 32bit hash. What I mean is that the database stores
        private String password;// the encoded string (ie: 497f3hmw8ox7ghn) instead of the name. This class can encode it and compare that. 
        bool active; // if this person is active. inactive accounts can still be stored
        String suffix; // mr, ms, etc. 
        int approxHours; // how many hours this person wants to work
        private String homePhone;
        private String cellPhone;
        private String address;
        bool manager;

        List<int> skillTableIDs;
        List<int> titleTableIDs;
        List<int> departmentTableIDs;
        int superiorID; // id to the superior of this person
        int paygradeTableID;
        int loginID; // to check current login in the login table

        DatabaseConnection dbc; // database connection object
        Timer timer;

        #region deprecated vars
        /*
        //List<Skill> skills;
        //List<Title> titles;
        //List<Department> assignedDepartments;
        //PayGrade paygrade;
         * Person superior; // the person above this person
         * //Schedule schedulePointer; // no longer needed
         * // protected string connectionString; // string used to connect to database. passes in location, password, etc
         */
        #endregion

        #endregion

        #region PROPERTIES
        //==============================================================================
        //                                                                    PROPERTIES
        //==============================================================================
        public Timer getTimer
        {
            get { return timer; }
            set { timer = value; }
        }

        public int SuperiorID
        {
            get { return superiorID; }
            set { superiorID = value; }
        }

        public int PayGradeTableID
        {
            get { return paygradeTableID; }
            set { paygradeTableID = value; }
        }

        public List<int> DepartmentTableIDs
        {
            get { return departmentTableIDs; }
            set { departmentTableIDs = value; }
        }

        public List<int> TitleTableIDs
        {
            get { return titleTableIDs; }
            set { titleTableIDs = value; }
        }

        public List<int> SkillTableIDs
        {
            get { return skillTableIDs; }
            set { skillTableIDs = value; }
        }

        public String Address
        {
            get { return address; }
            set { address = value; }
        }

        public DatabaseConnection DBC
        {
            get { return dbc; }
            set { dbc = value; }
        }

        public String FullName
        {
            get { return firstName + " " + middleName + " " + lastName; }
        }

        /// <summary>
        /// the target approximate hours this person wants to work
        /// </summary>
        public int ApproxHours
        {
            get { return approxHours; }
            set { approxHours = value; }
        }

        public String Suffix
        {
            get { return suffix; }
            set { suffix = value; }
        }

        public int PERID
        {
            get { return perid; }
            set { perid = value; }
        }

        public bool Active
        {
            get { return active; }
            set { active = value; }
        }

        public String Password
        {
            get { return password; }
            set { password = value; }
        }

        public String Username
        {
            get { return username; }
            set { username = value; }
        }

        public String FirstName
        {
            get { return firstName; }
            set { firstName = value; }
        }

        public String MiddleName
        {
            get { return middleName; }
            set { middleName = value; }
        }

        public String LastName
        {
            get { return lastName; }
            set { lastName = value; }
        }

        public String HomePhone
        {
            get { return homePhone; }
            set { homePhone = value; }
        }

        public String CellPhone
        {
            get { return cellPhone; }
            set { cellPhone = value; }
        }

        public bool IsManager
        {
            get { return manager; }
            set { manager = value; }
        }


        #region deprecated properties
        /*
        public Schedule SchedulePointer
        {
            get { return schedulePointer; }
            set { schedulePointer = value; }
        }
        public Person Superior
        {
            get { return superior; }
            set { superior = value; }
        }

        public PayGrade PayGrade
        {
            get { return paygrade; }
            set { paygrade = value; }
        }

        public List<Title> Titles
        {
            get { return titles; }
            set { titles = value; }
        }
        public List<Skill> Skills
        {
            get { return skills; }
            set { skills = value; }
        }

        public List<Department> AssignedDepartments
        {
            get { return assignedDepartments; }
            set { assignedDepartments = value; }
        }
        /*public String ConnectionString
        {
            get { return dbc.ConnectionString; }
            set { dbc.ConnectionString = value; }
        }
         * 
        
         */
        #endregion


        #endregion

        #region CONSTRUCTOR
        //==============================================================================
        //                                                                   CONSTRUCTOR
        //==============================================================================

        public Person()
        {
            perid = -1; // default, shows its not on the list

            firstName = ""; // since we cant put in null value for this in the database, even by accident
            middleName = "";
            lastName = "";
            homePhone = "";
            cellPhone = "";
            username = "username";
            password = UTIL.MD5Hasher("password");
            active = true;
            this.approxHours = 40;
            this.dbc = new DatabaseConnection();
            address = "";
            manager = false;
            skillTableIDs = new List<int>();
            titleTableIDs = new List<int>();
            paygradeTableID = -1;
            departmentTableIDs = new List<int>();
            loginID = -1;
            timer = new Timer();
            suffix = "";

            #region old var initializations
            /*skills = new List<Skill>();
            titles = new List<Title>();
            assignedDepartments = new List<Department>();
            paygrade = new PayGrade();
             * this.superior = this; // by default, so it always points to something
             */
            #endregion

        }

        #endregion

        #region PRIVATE

        //==============================================================================
        //                                                                       PRIVATE
        //==============================================================================


        private void truncateStrings()
        {
            address = address.Substring(0, Math.Min(128, address.Length));
            firstName = firstName.Substring(0, Math.Min(64, firstName.Length));
            lastName = lastName.Substring(0, Math.Min(64, lastName.Length));
            middleName = middleName.Substring(0, Math.Min(64, middleName.Length));
            suffix = suffix.Substring(0, Math.Min(16, suffix.Length));
        }

        /// <summary>
        /// returns paygrade object (in case you want to load it separartely from the one already there?)
        /// </summary>
        /// <returns></returns>
        private PayGrade loadPaygrade()
        {
            try
            {
                PayGrade paygrade = new PayGrade();
                DataTable table = dbc.query("SELECT paid FROM personhaspaygraderelationship WHERE perid = '" + perid + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        paygrade.PayGradeID = (int)row["paid"];
                        paygrade.loadData();
                    }
                }
                //this.paygradeTableID = paygrade.PayGradeID;
                //this.paygrade = paygrade; // set it to the local var;
                return paygrade;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// removes all the instances of this Person from any Person Relationship Table. 
        /// </summary>
        /// <returns></returns>
        private int removePersonRelations()
        {
            try
            {
                if (perid != -1)
                {
                    dbc.executeNonQuery("DELETE FROM personcalloffdaterelationship WHERE perid='" + perid + "'");
                    dbc.executeNonQuery("DELETE FROM personhasassaignedshiftrelationship WHERE perid='" + perid + "'");
                    dbc.executeNonQuery("DELETE FROM personhasdepartmentrelationship WHERE perid='" + perid + "'");
                    dbc.executeNonQuery("DELETE FROM personhaspaygraderelationship WHERE perid='" + perid + "'");
                    dbc.executeNonQuery("DELETE FROM personhaspreferredshiftrelationship WHERE perid='" + perid + "'");
                    dbc.executeNonQuery("DELETE FROM personhasskillrelationship WHERE perid='" + perid + "'");
                    dbc.executeNonQuery("DELETE FROM personhassuperiorrelationship WHERE peridA='" + perid + "'");
                    dbc.executeNonQuery("DELETE FROM personhassuperiorrelationship WHERE peridB='" + perid + "'");
                    dbc.executeNonQuery("DELETE FROM personhastitlerelationship WHERE perid='" + perid + "'");
                    dbc.executeNonQuery("DELETE FROM personhaspreferreddepartmentrelationship WHERE perid='" + perid + "'");
                    dbc.executeNonQuery("DELETE FROM personvacationdaterelationship WHERE perid='" + perid + "'");
                    dbc.executeNonQuery("DELETE FROM departmentmanagedbypersonrelationship WHERE perid='" + perid + "'");
                    dbc.executeNonQuery("DELETE FROM personhasassaignedshiftrelationship WHERE perid='" + perid + "'");
                    return 0;
                } return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns whether or not there is an instance of this person in a relationship table
        /// </summary>
        /// <returns>true if person is being used somewhere, false otherwise</returns>
        private bool checkPersonUsed()
        {
            try
            {
                if (perid != -1)
                {
                    DataTable table = dbc.query("SELECT * FROM personcalloffdaterelationship WHERE perid='" + perid + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    table = dbc.query("SELECT * FROM personhasassaignedshiftrelationship WHERE perid='" + perid + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    table = dbc.query("SELECT * FROM personhasdepartmentrelationship WHERE perid='" + perid + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    table = dbc.query("SELECT * FROM personhaspaygraderelationship WHERE perid='" + perid + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    table = dbc.query("SELECT * FROM personhaspreferredshiftrelationship WHERE perid='" + perid + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    table = dbc.query("SELECT * FROM personhasskillrelationship WHERE perid='" + perid + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    table = dbc.query("SELECT * FROM personhassuperiorrelationship WHERE peridA='" + perid + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    table = dbc.query("SELECT * FROM personhassuperiorrelationship WHERE peridB='" + perid + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    table = dbc.query("SELECT * FROM personhastitlerelationship WHERE perid='" + perid + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    table = dbc.query("SELECT * FROM personhaspreferreddepartmentrelationship WHERE perid='" + perid + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    table = dbc.query("SELECT * FROM personvacationdaterelationship WHERE perid='" + perid + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    table = dbc.query("SELECT * FROM departmentmanagedbypersonrelationship WHERE perid='" + perid + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;
                }
                return false;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// whether or not this person exists in the database
        /// </summary>
        /// <returns></returns>
        private bool checkPersonExists()
        {
            try
            {
                DataTable table = dbc.query("SELECT perid FROM Person WHERE username = '" + username + "' AND active = '" + UTIL.boolToInt(active) + "'");
                return ((table != null) && (table.Rows.Count > 0)) ? (true) : (false);
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// gets the ID from the database on the row matching this instance
        /// </summary>
        /// <returns>ID number if found, -1 if not found</returns>
        private int findPersonID()
        {
            try
            {
                DataTable table = dbc.query("SELECT perid FROM Person WHERE firstname = '" + firstName + "' AND middlename = '" + middleName + "' AND sirname = '" + lastName + "' AND approxhours  = '" + approxHours + "' AND username = '" + username + "' AND password = '" + password + "' AND suffix = '" + suffix + "' AND active = '" + UTIL.boolToInt(active) + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        return (int)row["perid"];
                    }
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        #region deprecated private functions


        /// <summary>
        /// if there is a title of Manager, it will remove it from the list
        /// </summary>
        private void deprecated_removeManagerStatus()
        {
            /* for (int i = 0; i <= titles.Count - 1; i++)
             {
                 if (titles[i].Name.CompareTo("Manager") == 0)
                 {
                     titles.RemoveAt(i);
                 }
             }*/
        }

        /// <summary>
        /// makes this person a manager by giving him that title
        /// </summary>
        private void deprecated_makeManager()
        {
            Title title = new Title();
            title.Name = "Manager";
            title.TitleID = -1;
            //titles.Add(title);
        }

        /// <summary>
        /// returns whether or not this person holds the "Manager" title
        /// </summary>
        /// <returns></returns>
        private bool deprecated_hasManagerTitle()
        {
            foreach (Title t in loadTitles())
            {
                if (t.Name.CompareTo("Manager") == 0)
                    return true;
            }
            return false;
        }


        #endregion

        private void loadSuperiorRelationID()
        {
            try
            {
                DataTable table = dbc.query("SELECT peridB FROM personhassuperiorrelationship WHERE peridA='" + perid + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        superiorID = (int)row["peridB"];
                    }
                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// sets the title relation IDs for this person
        /// </summary>
        private void loadTitleRelationIDs()
        {
            try
            {
                DataTable table = dbc.query("SELECT tid FROM personhastitlerelationship WHERE perid='" + perid + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        titleTableIDs.Add((int)row["tid"]);
                    }
                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// sets the skill relation ids for this person
        /// </summary>
        private void loadSkillRelationIDs()
        {
            try
            {
                DataTable table = dbc.query("SELECT sid FROM personhasskillrelationship WHERE perid='" + perid + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        skillTableIDs.Add((int)row["sid"]);
                    }
                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// sets the department relation ids for this person
        /// </summary>
        private void loadDepartmentRelationIDs()
        {
            try
            {
                DataTable table = dbc.query("SELECT deid FROM personhasdepartmentRelationship WHERE perid='" + perid + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        departmentTableIDs.Add((int)row["deid"]);
                    }
                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// sets the paygrade is relation for this person
        /// </summary>
        private void loadPayGradeRelationID()
        {
            try
            {
                DataTable table = dbc.query("SELECT paid FROM personhaspaygraderelationship WHERE perid='" + perid + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        paygradeTableID = ((int)row["paid"]);
                        break;
                    }
                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }

        }

        /// <summary>
        /// if the person is not logged in then try to, and get the login ID
        /// </summary>
        private void tryLoggingIn()
        {
            try
            {
                if (loginID == -1)
                {
                    DateTime latestCheckInTime = new DateTime();

                    //find latest checkin time for this person
                    DataTable table = dbc.query("SELECT checkintime FROM Login WHERE perid='" + this.PERID + "'");
                    if ((table != null) && (table.Rows.Count > 0))
                    {
                        foreach (DataRow row in table.Rows)
                        {
                            if ((DateTime)row["checkintime"] > latestCheckInTime)
                            {
                                latestCheckInTime = (DateTime)row["checkintime"];
                            }
                        }
                        //find difference to now
                        TimeSpan checkinTimeSpan = DateTime.Now.Subtract(latestCheckInTime);

                        if (checkinTimeSpan.Seconds < 20)
                        {
                            throw new Exception("Someone else is logged in under this username");
                        }
                    }


                    //checks have passed, get a new login
                    DateTime now = DateTime.Now;
                    dbc.executeNonQuery("INSERT INTO Login (perid,logintime,checkintime) VALUES ('" + perid + "','" + now + "','" + now + "')");
                    DataTable table2 = dbc.query("SELECT loid FROM Login WHERE perid='" + perid + "' AND logintime='" + now + "' AND checkintime='" + now + "'");
                    if ((table2 != null) && (table2.Rows.Count > 0))
                    {
                        foreach (DataRow row in table2.Rows)
                        {
                            loginID = (int)row["loid"];
                            return;
                        }
                    }
                    throw new Exception("Error in database call to Login Table");

                }
                throw new Exception("You are already logged in. Why are you trying to log in again?");
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        #endregion

        #region PUBLIC
        //==============================================================================
        //                                                                        PUBLIC
        //==============================================================================

        public int writeOutObjectTree(int uniqueCounter, int sepCounter, int spaceIndex)
        {
            String spaces = "";

            for (int i = 0; i < spaceIndex; i++) { spaces = spaces + "\t"; }
            UTIL.sayDebug("(" + uniqueCounter + ")" + spaces + "(" + sepCounter + ")" + this.GetType().Name + "_" + this.perid);

            return uniqueCounter;
        }

        /// <summary>
        /// this person checks into the login table to say he is currently here
        /// </summary>
        public void checkIn()
        {
            if (loginID != -1)
            {
                dbc.executeNonQuery("UPDATE Login SET checkintime='" + DateTime.Now + "' WHERE loid='" + loginID + "'");
            }
            else throw new Exception("Person is not logged in yet.");
        }


        /// <summary>
        /// returns whether or not this person is logged into the database
        /// </summary>
        /// <returns>true or false</returns>
        public bool IsLoggedIn()
        {
            try
            {
                if (loginID != -1)
                {
                    DataTable table = dbc.query("SELECT checkintime FROM Login WHERE loid='" + loginID + "'");
                    if ((table != null) && (table.Rows.Count > 0))
                    {
                        foreach (DataRow row in table.Rows)
                        {
                            DateTime lastCheckIn = (DateTime)row[0];
                            TimeSpan span = lastCheckIn - DateTime.Now;
                            if (span.Minutes < 1) return true;
                        }
                    }
                    return false;
                }
                else throw new Exception("You have never logged in");
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns the list of titles that this person has
        /// </summary>
        /// <returns></returns>
        public List<Title> loadTitles()
        {
            try
            {
                List<Title> titles = new List<Title>();
                DataTable table = dbc.query("SELECT tid FROM personhastitlerelationship WHERE perid = '" + perid + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Title title = new Title();
                        title.TitleID = (int)row["tid"];
                        title.loadData();
                        titles.Add(title);
                    }
                }
                return titles;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns the DATETIME values of the vacation days
        /// </summary>
        /// <returns></returns>
        public List<DateTime> loadVacationDates()
        {
            try
            {
                List<DateTime> dtList = new List<DateTime>();
                DataTable table = dbc.query("SELECT D.d FROM D, personvacationdaterelationship R WHERE R.perid = '" + this.perid + "' AND R.did = D.did");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        DateTime dt = (DateTime)row["d"];
                        dtList.Add(dt);
                    }
                }

                return dtList;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns a list of all the managers
        /// </summary>
        /// <returns></returns>
        public static List<Person> loadAllManagers()
        {
            try
            {
                DatabaseConnection dbc = new DatabaseConnection();
                List<Person> persons = new List<Person>();
                DataTable table = dbc.query("SELECT perid FROM Person WHERE manager=1");
                if ((table != null) && (table.Rows.Count > 0))
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Person person = new Person();
                        person.perid = (int)row["perid"];
                        person.loadData();
                        persons.Add(person);
                    }
                }
                return persons;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// loads every nurse even if they are inactive.
        /// </summary>
        /// <returns></returns>
        public static List<Person> loadAllNurses()
        {
            try
            {
                DatabaseConnection dbc = new DatabaseConnection();
                List<Person> persons = new List<Person>();
                DataTable table = dbc.query("SELECT perid FROM Person");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Person person = new Person();
                        person.PERID = (int)row["perid"]; // assign the personID
                        // person.connectionString = dbc.ConnectionString; // default one
                        person.loadData();
                        persons.Add(person);
                    }
                }
                return persons;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns a list of all active persons in the database
        /// this list is separate from teh schedule's master list and therefore are not pointers to it
        /// </summary>
        /// <returns></returns>
        public static List<Person> loadAllActiveNurses()
        {
            try
            {
                DatabaseConnection dbc = new DatabaseConnection();
                List<Person> persons = new List<Person>();
                DataTable table = dbc.query("SELECT perid FROM Person WHERE active = 1");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Person person = new Person();
                        person.PERID = (int)row["perid"]; // assign the personID
                        // person.connectionString = dbc.ConnectionString; // default one
                        person.loadData();
                        persons.Add(person);
                    }
                }
                return persons;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// Returns an instance of the Person class regardless of whether or not the login information was correct. 
        /// For any database connection problem, it will throw an Exception.
        /// If the username and password were not included in the database the returning Person ID will be -1, 
        /// meaning it was not loaded. 
        /// </summary>
        /// <param name="username">the account's username</param>
        /// <param name="password">the account's unhashed password</param>
        /// <returns>Person Object. ID=-1 if not loaded, ID=tableID otherwise</returns>
        public static Person login(String username, String password)
        {
            try
            {
                //check for sql injections
                if ((!UTIL.isStringSafeFromSQLInjection(username)) || (!UTIL.isStringSafeFromSQLInjection(password)))
                {
                    throw new Exception("BAD LOGIN INFORMATION");
                }

                Person person = new Person();

                person.Username = username;
                person.Password = UTIL.MD5Hasher(password);

                DatabaseConnection dbc = new DatabaseConnection();
                DataTable table = dbc.query("SELECT perid FROM Person WHERE username = '" + person.Username + "' AND password = '" + person.Password + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        person.PERID = (int)row[0]; // assign the personID
                        person.tryLoggingIn();
                        person.loadData();

                        break;
                    }
                }
                return person;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// Just saves the Person's class data without the relationships.
        /// Eric - Wait, isn't this a bad thing? Don't we need to know the person's department, paygrade, etc?
        /// </summary>
        /// <returns></returns>
        public int saveDataSimple()
        {
            try
            {
                truncateStrings(); // makes strings short if they are too long

                if (this.perid == -1)
                {
                    if (!checkPersonExists())
                    {
                        password = UTIL.MD5Hasher(password); // password has not been hashed yet. this is the one and only time this will happen
                        dbc.executeNonQuery("INSERT INTO Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address, active, manager) values ('" + firstName + "','" + middleName + "','" + lastName + "','" + approxHours + "','" + username + "','" + password + "','" + suffix + "','" + homePhone + "','" + cellPhone + "','" + address + "','" + UTIL.boolToInt(active) + "','" + UTIL.boolToInt(manager) + "')");
                    }
                    this.perid = findPersonID();
                }
                else
                {
                    //save
                    dbc.executeNonQuery("UPDATE Person SET firstname='" + firstName + "', middlename='" + middleName + "', sirname='" + lastName + "', approxHours='" + approxHours + "', username='" + username + "', password='" + password + "', suffix='" + suffix + "', homephone='" + homePhone + "', cellphone='" + cellPhone + "', active='" + UTIL.boolToInt(active) + "', address='" + address + "', manager='" + UTIL.boolToInt(manager) + "' WHERE perid='" + perid + "'");
                }

                return this.perid;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// Saves the Person class's data into the database, along with the relations
        /// Does NOT save the lower level objects
        /// </summary>
        /// <returns>the Person Table ID, in the case that the person was not in the table before, now you know it's index</returns>
        public int saveData()
        {
            try
            {
                truncateStrings(); // makes strings short if they are too long

                if (this.perid == -1)
                {
                    if (!checkPersonExists())
                    {
                        password = UTIL.MD5Hasher(password); // password has not been hashed yet. this is the one and only time this will happen
                        dbc.executeNonQuery("INSERT INTO Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address, active, manager) values ('" + firstName + "','" + middleName + "','" + lastName + "','" + approxHours + "','" + username + "','" + password + "','" + suffix + "','" + homePhone + "','" + cellPhone + "','" + address + "','" + UTIL.boolToInt(active) + "','" + UTIL.boolToInt(manager) + "')");
                    }
                    else
                    {
                        return -2; // cant use an existing username. 
                    }
                    this.perid = findPersonID();
                }
                else
                {
                    //save
                    dbc.executeNonQuery("UPDATE Person SET firstname='" + firstName + "', middlename='" + middleName + "', sirname='" + lastName + "', approxHours='" + approxHours + "', username='" + username + "', password='" + password + "', suffix='" + suffix + "', homephone='" + homePhone + "', cellphone='" + cellPhone + "', active='" + UTIL.boolToInt(active) + "', address='" + address + "', manager='" + UTIL.boolToInt(manager) + "' WHERE perid='" + perid + "'");
                }

                //-------------------------------------
                // remove superior relations
                dbc.executeNonQuery("DELETE FROM personhassuperiorrelationship WHERE peridA = '" + perid + "'");

                //save new relationship
                dbc.executeNonQuery("INSERT INTO personhassuperiorrelationship (peridA,peridB) values ('" + perid + "','" + superiorID + "')");

                //-------------------------------------


                //-------------------------------------
                //remove old skill relationship
                dbc.executeNonQuery("DELETE FROM personhasskillrelationship WHERE perid = '" + perid + "'");

                foreach (int skillTableID in skillTableIDs)
                {
                    //save new relationship
                    dbc.executeNonQuery("INSERT INTO personhasskillrelationship (perid,sid) values ('" + perid + "','" + skillTableID + "')");
                }

                //-------------------------------------

                //-------------------------------------
                dbc.executeNonQuery("DELETE FROM personhasdepartmentrelationship WHERE perid = '" + perid + "'");

                foreach (int departmentTableID in departmentTableIDs)
                {
                    //save new relationship
                    dbc.executeNonQuery("INSERT INTO personhasdepartmentrelationship (perid,deid) values ('" + perid + "','" + departmentTableID + "')");
                }

                /* foreach (Department d in assignedDepartments) { //departments themselves need no save because they are above Person on the Hierarchy. 
                    dbc.executeNonQuery("INSERT INTO personhasdepartmentrelationship (perid,deid) values ('" + perid + "','" + d.DepartmentID + "')"); //save new relationship      }*/

                //-------------------------------------

                //-------------------------------------
                dbc.executeNonQuery("DELETE FROM personhastitlerelationship WHERE perid = '" + perid + "'");

                foreach (int titleTableID in titleTableIDs)
                {
                    //save new relationship
                    dbc.executeNonQuery("INSERT INTO personhastitlerelationship (perid,tid) values ('" + perid + "','" + titleTableID + "')");
                }

                //-------------------------------------

                //-------------------------------------

                if (paygradeTableID != -1)
                {
                    //remove old relationship
                    dbc.executeNonQuery("DELETE FROM personhaspaygraderelationship WHERE perid = '" + perid + "'");
                    //save new relationship
                    dbc.executeNonQuery("INSERT INTO personhaspaygraderelationship (perid,paid) values ('" + perid + "','" + paygradeTableID + "')");
                }
                /* if (paygrade.PayGradeID != -1)  {   //remove old relationship
                    dbc.executeNonQuery("DELETE FROM personhaspaygraderelationship WHERE perid = '" + perid + "'");   }  int paid = paygrade.saveData();//save
                dbc.executeNonQuery("INSERT INTO personhaspaygraderelationship (perid,paid) values ('" + perid + "','" + paid + "')");//save new relationship   */
                //-------------------------------------

                return this.perid;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// removes this Person from the table, after removing all connections to outside things. 
        /// </summary>
        /// <returns></returns>
        public int removeData()
        {
            try
            {
                if (perid != -1)
                {
                    removePersonRelations();
                    if (!checkPersonUsed())
                    {
                        dbc.executeNonQuery("DELETE FROM Person WHERE perid='" + perid + "'");
                        return 0;
                    }
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// Loads a person from the Person table with the specified perid.
        /// This has been changed to "loadPerson" because it loads and returns a Person.
        /// </summary>
        public static Person loadPerson(int perid)
        {
            Person person = new Person();
            person.perid = perid;
            person.loadData();
            return person;
        }

        /// <summary>
        /// Loads all the data necessary for this class from it's own table
        /// Like the others, make sure  you set the ID and the ConnectionString before loading
        /// Does NOT load the objects that the Person class owns, but just a list of their tableIDs
        /// </summary>
        public void loadData()
        {
            try
            {
                DataTable table = dbc.query("SELECT * FROM Person P WHERE P.perid = '" + perid + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        username = (string)row["username"];
                        firstName = (string)row["firstname"];
                        middleName = (string)row["middlename"];
                        lastName = (string)row["sirname"];
                        approxHours = (int)row["approxHours"];
                        suffix = (string)row["suffix"];
                        homePhone = (string)row["homephone"];
                        cellPhone = (string)row["cellphone"];
                        address = (string)row["address"];
                        active = (Convert.ToBoolean((short)row["active"]));
                        manager = Convert.ToBoolean((short)row["manager"]);

                        loadTitleRelationIDs();
                        loadSkillRelationIDs();
                        loadDepartmentRelationIDs();
                        loadPayGradeRelationID();
                        loadSuperiorRelationID();

                        #region loading old vars
                        /*titles = this.loadTitles();
                        skills = this.getSkills();
                        assignedDepartments = this.getAssignedDepartments();
                        paygrade = this.loadPaygrade();*/
                        #endregion

                    }
                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns a list of hte shifts that this person preferrs to work
        /// </summary>
        /// <returns></returns>
        public List<Shift> loadPreferredShifts()
        {
            try
            {
                List<Shift> shifts = new List<Shift>();

                DataTable table = dbc.query("SELECT shid FROM personhaspreferredshiftrelationship WHERE perid ='" + perid + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Shift s = new Shift();
                        // s.ConnectionString = this.connectionString;
                        s.ShiftID = (int)row[0];
                        s.loadData();
                        shifts.Add(s);
                    }
                }
                return shifts;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// gets all the skills that this person has
        /// </summary>
        /// <returns></returns>
        public List<Skill> loadSkills()
        {
            try
            {
                List<Skill> skills = new List<Skill>();
                DataTable table = dbc.query("SELECT sid FROM personhasskillrelationship WHERE perid = '" + perid + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Skill s = new Skill();
                        // s.ConnectionString = this.connectionString;
                        s.SkillID = (int)row["sid"];
                        s.loadData();
                        skills.Add(s);
                    }
                }
                return skills;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// sums up all the departments that this person deals with and removes redundancies
        /// </summary>
        /// <returns></returns>
        public List<Department> loadAllRelatedDepartments()
        {
            try
            {
                List<Department> list = new List<Department>();
                list.AddRange(loadAssignedDepartments());
                list.AddRange(loadPreferredDepartments());

                for (int q = 0; q <= list.Count - 2; q++)
                {
                    for (int w = q + 1; w <= list.Count - 1; w++)
                    {
                        if ((list[q].DepartmentID) == (list[w].DepartmentID))
                        {
                            list.RemoveAt(w);
                            w--;
                        }
                    }
                }
                return list;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns all the departments that this person is assigned to work under
        /// </summary>
        /// <returns></returns>
        public List<Department> loadAssignedDepartments()
        {
            try
            {
                List<Department> departments = new List<Department>();

                DataTable table = dbc.query("SELECT deid FROM personhasdepartmentrelationship WHERE perid ='" + perid + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Department d = new Department();
                        d.DepartmentID = (int)row["deid"];
                        d.loadData();
                        departments.Add(d);
                    }
                }
                return departments;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns a list of the preferred departments that this person wants to work under
        /// </summary>
        /// <returns></returns>
        public List<Department> loadPreferredDepartments()
        {
            try
            {
                List<Department> departments = new List<Department>();

                //preferred department is misspelled in the table name so its mispelled here too
                DataTable table = dbc.query("SELECT deid FROM personhaspreferreddepartmentrelationship WHERE perid ='" + perid + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Department d = new Department();
                        d.DepartmentID = (int)row["deid"];
                        d.loadData();
                        departments.Add(d);
                    }
                }
                return departments;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns all the called off days, basically the same as the vacation days
        /// </summary>
        /// <returns></returns>
        public List<Day> loadCallOffDays()
        {
            try
            {
                List<Day> days = new List<Day>();

                DataTable table = dbc.query("SELECT did FROM personcalloffdaterelationship WHERE perid ='" + perid + "'");
                foreach (DataRow row in table.Rows)
                {
                    Day d = new Day();
                    d.DID = (int)row["did"];
                    d.loadData();
                    days.Add(d);
                }
                return days;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns all the vacations days that are in the databse for this person
        /// </summary>
        /// <returns></returns>
        public List<Day> loadVacationDays()
        {
            try
            {
                List<Day> days = new List<Day>();

                DataTable table = dbc.query("SELECT did FROM personvacationdaterelationship V WHERE V.perid ='" + perid + "'");
                foreach (DataRow row in table.Rows)
                {
                    Day d = new Day();
                    d.DID = (int)row["did"];
                    d.loadData();
                    days.Add(d);
                }
                return days;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        #region deprecated functions

        /*///get availabletimes
        public List<deprecated_AvailableTime> deprecated_getAvailableTimes()
        {
            List<deprecated_AvailableTime> list = new List<deprecated_AvailableTime>();

            DataTable table = dbc.query("SELECT aid FROM personhasavailablerelationship WHERE perid = '" + perid + "'");

            foreach (DataRow row in table.Rows)
            {
                deprecated_AvailableTime at = new deprecated_AvailableTime();
                at.AID = (int)row["aid"];
                at.PersonID = perid;
                //at.ConnectionString = this.connectionString;
                at.loadData(); // now that you know the ID of this specific available time, grab the data from the database
                list.Add(at);
            }

            list.Sort(); // make a IComparer object to compare these. right now its just default
            return list;
        }*/
        /*///get availabletimes
        public List<deprecated_PreferredTime> deprecated_getPreferredTimes()
        {
            List<deprecated_PreferredTime> list = new List<deprecated_PreferredTime>();

            DataTable table = dbc.query("SELECT pid FROM personhaspreferredrelationship WHERE perid = '" + perid + "'");

            foreach (DataRow row in table.Rows)
            {
                deprecated_PreferredTime pt = new deprecated_PreferredTime();
                pt.PID = (int)row["pid"];
                pt.PersonID = perid;
                // pt.ConnectionString = this.connectionString;
                pt.loadData();
                list.Add(pt);
            }

            list.Sort(); // make a IComparer object to compare these. right now its just default
            return list;
        }*/
        /*public void deprecated_removePerson(int workerID)
        {
            /*if (!checkPersonUsed(workerID))
            {

            }
        }*/
        /*public void deprecated_addPerson(String username)
        {
            /*if(checkDate(dateTime)==false){
                DatabaseConnection dbc = new DatabaseConnection();
                dbc.Query="INSERT INTO D (d) VALUES('" + dateTime +"')";
            } // end if
        } // end addDate
    */
        /*  public int deprecated_getWorkerID(String username)
          {
              //DatabaseConnection dbc = new DatabaseConnection();
              /*String sql = "SELECT did FROM D WHERE D.d = '" + dateTime + "'";
              //dbc.query = sql;
              //return dbc.getQuery(); // pull out the id from the result, however its formatted
              return -1;
          }*/
        #endregion

        #endregion

    } // end class


} //end namespace
